import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import webbrowser

# Load the Excel file
file_path = 'data/北京天气近十年_2015到2025.xlsx'
excel_data = pd.ExcelFile(file_path)

# Check sheet names to understand the structure
sheet_names = excel_data.sheet_names

# Load and clean data for all years
all_years_data = []

for year in sheet_names:
    df = excel_data.parse(year)

    # Check if '气温' is in the correct format
    df['气温'] = df['气温'].apply(
        lambda x: x if isinstance(x, str) and '℃' in x else None)  # Keep only valid temp strings

    # Handle missing or incorrect temperature values (keep only the rows with valid temperatures)
    df = df.dropna(subset=['气温'])

    # Now split the '气温' column into '最高温' and '最低温'
    df[['最高温', '最低温']] = df['气温'].str.extract(r'(\d+|-?\d+)℃/(\d+|-?\d+)℃')
    df['最高温'] = df['最高温'].astype(float)
    df['最低温'] = df['最低温'].astype(float)

    # Add a '年份' column to track the year
    df['年份'] = year

    # Append the cleaned data
    all_years_data.append(df)

# Concatenate all years' data into a single DataFrame
weather_data = pd.concat(all_years_data)

# Filter the data for summer (June-August) and winter (December-February) months
weather_data['日期'] = pd.to_datetime(weather_data['日期'])
weather_data['月份'] = weather_data['日期'].dt.month

# Filter data for summer (6-8) and winter (12-2)
summer_data = weather_data[weather_data['月份'].isin([6, 7, 8])]
winter_data = weather_data[weather_data['月份'].isin([12, 1, 2])]

# Create separate figures for Summer and Winter box plots

# Summer Maximum Temperature Plot
fig_summer = go.Figure()
fig_summer.add_trace(
    go.Box(x=summer_data["年份"], y=summer_data["最高温"], name="Summer Maximum Temperature", marker_color='blue')
)
fig_summer.update_layout(
    # title="Summer Maximum Temperature by Year",
    showlegend=False,
    height=500, width=600,
    # template="plotly_white"
    paper_bgcolor='rgba(0,0,0,0)',  # ✅ 整图背景透明
    plot_bgcolor='rgba(0,0,0,0)'  # ✅ 坐标轴区域背景透明
)

# Save the summer plot as an HTML file
summer_html_output = 'summer_temperature_analysis_2015_2025.html'
fig_summer.write_html(summer_html_output)

# Winter Minimum Temperature Plot
fig_winter = go.Figure()
fig_winter.add_trace(
    go.Box(x=winter_data["年份"], y=winter_data["最低温"], name="Winter Minimum Temperature", marker_color='orange')
)
fig_winter.update_layout(
    # title="Winter Minimum Temperature by Year",
    showlegend=False,
    height=500, width=600,
    # template="plotly_white"
    paper_bgcolor='rgba(0,0,0,0)',  # ✅ 整图背景透明
    plot_bgcolor='rgba(0,0,0,0)'  # ✅ 坐标轴区域背景透明
)

# Save the winter plot as an HTML file
winter_html_output = 'winter_temperature_analysis_2015_2025.html'
fig_winter.write_html(winter_html_output)

# Automatically open both HTML files in the default web browser
webbrowser.open(summer_html_output)
webbrowser.open(winter_html_output)
